<%@page import="java.sql.CallableStatement"%>
<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" %> 
<%@page import="connection.DBConnection"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Profit And Loss Account</title>
</head>
<body>
<form name="profitandloss">

  <%
        String exportToExcel = request.getParameter("exportToExcel");
        if (exportToExcel != null && exportToExcel.toString().equalsIgnoreCase("YES")) 
        {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "inline; filename=" + "PNLReport.xls");
 
        }
    %>

<%
 
		DBConnection dbc=new DBConnection();
		Connection con=dbc.getNewConnection();
		
		Statement st = null;
		ResultSet rs = null;   
		CallableStatement cs = null;
		
		double openingstock=0.0;
		double administrativecharges=0.0;
		double purchase=0.0, purchasetransport=0.0, purchaseothercharges=0.0;		
		double debit=0.0, debittransport=0.0, debitothercharges=0.0;
		double sales=0.0,salestransport=0.0;
		double credit=0.0,credittransport=0.0;
		double closingstock=0.0;
		double bankcharges=0.0;
		double indirectincome=0.0;		
		double salarywages=0.0;		
		double sellingandmarketing=0.0;		
		double depreciation=0.0;		
		double otherexpenses=0.0;		
		
		try
   		{
		   cs=con.prepareCall("{call PNL2}");
		   rs=cs.executeQuery();
		   
		 	while(rs.next())
		 	{
			    String a1=rs.getString(1); // group name
			    double a2=rs.getDouble(2); // amount
			    
			    out.print("<br>"+ a1 +":"+ a2);
			    
			    if(a1.equals("Opening Stock"))
			   	{
				   openingstock=a2;
			   	}
			    else if(a1.equals("closing Stock"))
			   	{
				   closingstock=a2;
			   	}
			    else if(a1.equals("Administrative Charges"))
			   	{
				   administrativecharges=a2;
			   	}
			  	else if(a1.equals("Bank Charges and Interst"))
			  	{
			  		bankcharges=a2;
			  	}
			  	else if(a1.equals("Indirect Income"))
			  	{
			  		indirectincome=a2;
			  	}
			  	else if(a1.equals("Selling and Marketing Expenses"))
			  	{
			  		sellingandmarketing=a2;
			  	}
			  	else if(a1.equals("Salary and Wages"))
			  	{
			  		salarywages=a2;
			  	}
			  	else if(a1.equals("Depreciation"))
			  	{
			  		depreciation=a2;
			  	}
			  	else if(a1.equals("Other Expenses"))
			  	{
			  		otherexpenses=a2;
			  	}
			  	else if(a1.equals("Bank Charges and Interest"))
			  	{
			  		bankcharges=a2;
			  	}
			  	else if(a1.equals("Sales Net"))
			  	{
			  		sales=a2;
			  	}
			  	else if(a1.equals("Sales Transport"))
			  	{
			  		salestransport=a2;
			  	}
			  	else if(a1.equals("Credit Net"))
			  	{
			  		credit=a2;
			  	}
			  	else if(a1.equals("Credit Transport"))
			  	{
			  		credittransport=a2;
			  	}
			  	else if(a1.equals("Purchase Net"))
			  	{
			  		purchase=a2;
			  	}
			  	else if(a1.equals("Purchase Transport"))
			  	{
			  		purchasetransport=a2;
			  	}
			  	else if(a1.equals("Purchase Other"))
			  	{
			  		purchaseothercharges=a2;
			  	}
			  	else if(a1.equals("Debit Net"))
			  	{
			  		debit=a2;
			  	}
			  	else if(a1.equals("Debit Transport"))
			  	{
			  		debittransport=a2;
			  	}
			  	else if(a1.equals("Debit Other"))
			  	{
			  		debitothercharges=a2;
			  	}

		 	}
		  
   		}
		catch(Exception e) {}
		
		
		
%>
<table align="center" border=5 width=700>

<tr>
	<th colspan=4><font color=skyblue>Profit and Loss Account</font></th>
</tr>

<tr>
	<th><font color=skyblue>Expenses</font></th>
	<th><font color=skyblue>Rs.</font></th>
	<th><font color=skyblue>Income</font></th>
	<th><font color=skyblue>Rs.</font></th>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>


<tr>
	<td>To Opening Stock</td>
	<td><%=openingstock %></td>
	

	<td>By Sales</td>
	<td><%=sales-credit %></td>
</tr>

<tr>
	<td>To Purchases</td>
	<td><%=purchase-debit %></td>
	
	<td>By Sales Transport Charges</td>
	<td><%=salestransport-credittransport %></td>
</tr>

<tr>
	<td>To Purchase Transport Charges</td>
	<td><%=purchasetransport-debittransport %></td>

	
	<td>By Closing Stock</td>
	<td><%=closingstock %></td>
</tr>

<tr>
	<td>To Purchase Other Expenses</td>
	<td><%=purchaseothercharges-debitothercharges %></td>
	<td></td>
	<td></td>
</tr>

<% 		double grossprofit=0.0;
		// Upper RHS 
		double Total2=salestransport+closingstock+sales-credit-credittransport;
		
		//Upper LHS/
		double Subtotal1=openingstock+purchase+purchasetransport+purchaseothercharges-debit-debittransport-debitothercharges;
	
		grossprofit=Total2-Subtotal1;
	
		double Total1=grossprofit+Subtotal1;
		//grossprofit=sales+salestransport+closingstock-openingstock-purchase-purchasetransport-purchaseothercharges;
		
	
%>

<tr>
	<td>To Gross Profit</td>
	<td><%=grossprofit %></td>
	<td></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<tr>
	<td>Total1</td>
	<td><%=Total1 %></td>
	<td>Total2</td>
	<td><%=Total2 %></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>


<tr>
	<td>To Administrative Charges</td>
	<td><%=administrativecharges %></td>
	<td>By Gross Profit</td>
	<td><%=grossprofit %></td>
</tr>


<tr>
	<td>To Bank Charges and Interest</td>
	<td><%=bankcharges %></td>
	

	<td>By Indirect Income</td>
	<td><%=indirectincome %></td>
</tr>

<tr>
	<td>To Selling and Marketing Expenses</td>
	<td><%=sellingandmarketing %></td>
	<td></td>
	<td></td>
</tr>

<tr>
	<td>To Salary Wages</td>
	<td><%=salarywages %></td>
	<td></td>
	<td></td>
</tr>

<tr>
	<td>To Depreciation</td>
	<td><%=depreciation %></td>
	<td></td>
	<td></td>
</tr>

<tr>
	<td>To Other Expenses</td>
	<td><%=otherexpenses %></td>
	<td></td>
	<td></td>
</tr>

<%  	double netprofit=0.0;
		
		double Total3=grossprofit+indirectincome;
		double subtotal4=administrativecharges+bankcharges+sellingandmarketing+salarywages+depreciation+otherexpenses;
		
		netprofit= grossprofit+indirectincome-administrativecharges-bankcharges-sellingandmarketing-salarywages-depreciation-otherexpenses;
		
		double Total4=netprofit+subtotal4;
%>
<tr>
	<td>To Net Profit</td>
	<td><%=netprofit %></td>
	<td></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<tr>
	<td>Total3</td>
	<td><%=Total4 %></td>
	<td>Total4</td>
	<td><%=Total3 %></td>
</tr>
</table>
<br><br> 
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

    <%
        if (exportToExcel == null) {
    %>
    <a href="ProfitAndLossAccount2.jsp?exportToExcel=YES">Export to Excel</a>
    <%
        }
    %>
    
    <center><input type="button" value="Print this page" onClick="window.print()"></center>
</form>

</body>
</html>